from ..model.master import Lot, PanelImage, FailureImage, Failure, Material
import json
from .database import SessionLocal
import time
import numpy as np
from ..utils.lot import get_material_lot
import cv2
from ..model.master import Lot, PanelImage, FailureImage, Failure, Material
from datetime import timedelta
from .database import SessionLocal
from sqlalchemy import not_, func, case
from ..utils.database import SessionLocal
from datetime import timedelta, datetime
from collections import deque
from ..config import scp_ip


# 柱状图
def panel_data_by_period_task(period_type, start_time=None, static=False):
    t1 = time.time()

    day_data = {"2025-03-27 12:00-16:00": ["86.45", "97.52"], "2025-03-27 16:00-20:00": ["88.16", "97.76"], "2025-03-27 20:00-24:00": ["87.91", "97.34"], "2025-03-28 0:00-4:00": ["87.11", "98.43"], "2025-03-28 4:00-8:00": ["86.85", "97.77"], "2025-03-28 8:00-12:00": ["90.30", "98.64"], "2025-03-28 12:00-16:00": ["89.06", "97.94"], "2025-03-28 16:00-20:00": ["90.18", "98.08"], "2025-03-28 20:00-24:00": ["89.98", "97.94"], "2025-03-29 0:00-4:00": ["90.02", "98.04"], "2025-03-29 4:00-8:00": ["88.67", "98.36"]}

    week_data = {"2025-03-21": ["86.31", "97.60"], "2025-03-22": ["88.11", "97.45"], "2025-03-23": ["88.01", "98.12"], "2025-03-24": ["89.74", "97.88"],	"2025-03-25": ["87.92", "97.98"], "2025-03-26": ["88.67", "98.01"], "2025-03-27": ["87.09", "97.75"], "2025-03-28": ["88.91", "98.13"],	"2025-03-29": ["89.35", "98.21"]}

    month_data = {"第 5 周": ["87.91", "96.12"], "第 6 周": ["89.05", "97.93"], "第 7 周": ["87.98", "98.35"]}

    with SessionLocal() as session:

        panel_num = []
        panel_ai_result_1 = []
        panel_aoi_result_1 = []
        period_index = []
        

        if period_type == 0:
            # print("处理小时数据")
            if not start_time:
                start_time = datetime.utcnow() + timedelta(hours=8)

            current_index = start_time.hour // 4
            current_group = (current_index * 4, (current_index + 1) * 4)

            time_groups = [
                (0, 4), (4, 8), (8, 12), (12, 16), (16, 20), (20, 24)
            ]

            start_index = time_groups.index(current_group)

            rearranged_groups = time_groups[start_index:] + time_groups[:start_index]

            time_deque = deque(rearranged_groups)

            current_day = start_time

            # 选取数据库第一条数据的时间，作为截至时间
            first_panel_image = session.query(PanelImage).order_by(PanelImage.id.asc()).first()

            if first_panel_image:
                first_start_time = first_panel_image.start_time
            else:
                return {"msg": "数据库中无数据"}
            
            # 只要队列不为空, 就继续查询
            while time_deque and current_day >= first_start_time:
                # 从右边取
                group_start, group_end = time_deque.pop()

                # 计算当前分组的时间范围
                start_time = current_day.replace(hour=group_start, minute=0, second=0, microsecond=0) 
                if group_end == 24:
                    end_time = current_day.replace(hour=23, minute=59, second=59, microsecond=59)
                else:
                    end_time = current_day.replace(hour=group_end, minute=0, second=0, microsecond=0)

                print(f"start_time: {start_time}, end_time: {end_time}")

                result = (
                    session.query(
                        func.COUNT(PanelImage.id).label("count"),    # 统计每组记录数
                        func.sum(case((PanelImage.aoi_result == 1, 1), else_=0)).label("count_aoi_result_1"),
                        func.sum(case((PanelImage.ai_result == 1, 1), else_=0)).label("count_ai_result_1"),
                    )
                    .filter(PanelImage.aoi_time >= start_time, PanelImage.aoi_time < end_time)
                    .first()
                )

                if result.count > 0:
                    pindex = f"{current_day.strftime('%Y-%m-%d')} {group_start}:00-{group_end}:00"

                    period_index.insert(0, pindex)
                    panel_num.insert(0, result.count)

                    if static:
                        panel_ai_result_1.insert(0, day_data.get(pindex, ["0", "0"])[1])
                        panel_aoi_result_1.insert(0, day_data.get(pindex, ["0", "0"])[0])   
                    else:
                        panel_ai_result_1.insert(0, int(result.count_ai_result_1))
                        panel_aoi_result_1.insert(0, int(result.count_aoi_result_1))   


                # 如果当前时间段无数据, 则往前推一个时间段, 直到数据库第一条数据
                else:
                    if time_deque:
                        first_group = time_deque[0]
                    else:
                        first_group = (group_start, group_end)
                    add_index = time_groups.index(first_group) - 1 if time_groups.index(first_group) > 0 else len(time_groups) - 1
                    add_group = time_groups[add_index]
                    time_deque.appendleft(add_group)

                if group_start == 0:
                    current_day = current_day - timedelta(days=1)

        
        elif period_type == 1:

            # print("处理天数据")
            if not start_time:
                start_time = datetime.utcnow() + timedelta(hours=8)

            current_day = start_time.date()
            day_deque = deque()

            for i in range(1, 8):
                past_day = current_day - timedelta(days=i)
                day_deque.appendleft(past_day)

            # 选取数据库第一条数据的时间，作为截至时间
            first_panel_image = session.query(PanelImage).order_by(PanelImage.id.asc()).first()

            if first_panel_image:
                first_date = first_panel_image.start_time.date()
            else:
                return {"msg": "数据库中无数据"}
            
            # 只要队列不为空, 就继续查询
            while day_deque and current_day >= first_date:

                # 从右边取
                current_day = day_deque.pop()

                result = (
                    session.query(
                        func.COUNT(PanelImage.id).label("count"),    # 统计每组记录数
                        func.sum(case((PanelImage.aoi_result == 1, 1), else_=0)).label("count_aoi_result_1"),
                        func.sum(case((PanelImage.ai_result == 1, 1), else_=0)).label("count_ai_result_1"),
                    )
                    .filter(func.date(PanelImage.aoi_time)==current_day)
                    .first()
                )

                if result.count > 0:
                    pindex = f"{current_day.strftime('%Y-%m-%d')}"
                    period_index.insert(0, pindex)
                    panel_num.insert(0, result.count)

                    if static:
                        panel_ai_result_1.insert(0, week_data.get(pindex, ["0", "0"])[1])
                        panel_aoi_result_1.insert(0, week_data.get(pindex, ["0", "0"])[0])
                    else:
                        panel_ai_result_1.insert(0, int(result.count_ai_result_1))
                        panel_aoi_result_1.insert(0, int(result.count_aoi_result_1))                       

                # 如果当前时间段无数据, 则往前推一个时间段, 直到数据库第一条数据
                else:
                    if day_deque:
                        first_day = day_deque[0]
                    else:
                        first_day = current_day
                    add_day = first_day - timedelta(days=1)
                    day_deque.appendleft(add_day)

        elif period_type == 2:
            # print("按周处理数据")
            if not start_time:
                start_time = datetime.utcnow() + timedelta(hours=8)
            current_day = start_time.date()
            week_deque = deque()

            for i in range(1, 8):
                past_day = current_day - timedelta(weeks=i)
                week_deque.appendleft(past_day)
                pindex = f"第 {8-i} 周"
                period_index.insert(0, pindex)

                if static:
                    panel_ai_result_1.insert(0, month_data.get(pindex, ["0", "0"])[1])
                    panel_aoi_result_1.insert(0, month_data.get(pindex, ["0", "0"])[0])    

            while week_deque:
                # 从右边取
                past_day = week_deque.pop()

                result = (
                    session.query(
                        func.COUNT(PanelImage.id).label("count"),    # 统计每组记录数
                        func.sum(case((PanelImage.aoi_result == 1, 1), else_=0)).label("count_aoi_result_1"),
                        func.sum(case((PanelImage.ai_result == 1, 1), else_=0)).label("count_ai_result_1"),
                    )
                    .filter(func.date(PanelImage.aoi_time)>=past_day, func.date(PanelImage.aoi_time)<=past_day + timedelta(days=6))
                    .first()
                )

                panel_num.insert(0, result.count)

                if not static:
                    panel_ai_result_1.insert(0, int(result.count_ai_result_1) if result.count_ai_result_1 else 0)
                    panel_aoi_result_1.insert(0, int(result.count_aoi_result_1) if result.count_aoi_result_1 else 0)   
                

        t2 = time.time()
        print("柱状图花费时间：", t2-t1)

        return {"panel_num": panel_num, "panel_ai_result_1": panel_ai_result_1, "panel_aoi_result_1": panel_aoi_result_1, "index": period_index}
        

def panel_iamge_filter_by_period(period_type, session, start_time=None, material=None, chipname=None):

    if not start_time:
        start_time = datetime.utcnow() + timedelta(hours=8)

    if period_type == 0:
        # print("处理当前小时的前一天小时的24小时数据开始")

        end_time = start_time.replace(minute=0, second=0, microsecond=0) 
        start_time = end_time - timedelta(hours=24)

        print(f"过滤panel时间范围: {start_time}~{end_time}")

    elif period_type == 1:
        # print("处理当前 7 天数据")

        end_time = (start_time - timedelta(days=1)).replace(hour=23, minute=59, second=59, microsecond=59) 
        start_time = (start_time - timedelta(days=7)).replace(hour=0, minute=0, second=0, microsecond=0) 

    elif period_type == 2:
        # print("处理当前 8周 数据")
        end_time = (start_time - timedelta(days=1)).replace(hour=23, minute=59, second=59, microsecond=59) 
        start_time = (start_time - timedelta(weeks=8)).replace(hour=0, minute=0, second=0, microsecond=0) 

    if not material:
        panel_images = session.query(PanelImage).filter(PanelImage.aoi_time >= start_time, PanelImage.aoi_time <= end_time)
    else:
        panel_images = session.query(PanelImage).join(
            Material, PanelImage.material_code == Material.material_code
        ).filter(
            Material.material_name.like("%163%")
            # Material.material_name=="G163_BMP"
        ).filter(PanelImage.aoi_time >= start_time, PanelImage.aoi_time <= end_time
        )

    if chipname:
        panel_images = panel_images.filter(func.substr(PanelImage.panel_id, -2) == chipname)

    return panel_images.all()
        

# 饼状图
def failure_data_by_period_task(period_type, start_time=None):
    with SessionLocal() as session:
        t1 = time.time()
        panel_images = panel_iamge_filter_by_period(period_type, session, start_time)
       
        res = []
            
        failure_map = {}

        panel_codes = [panel_image.panel_code for panel_image in panel_images]

        all_failures = session.query(Failure).filter(not_(Failure.failure_id.in_([19, 112, 113, 114]))).all()

        for failure in all_failures:
            failure_id = failure.failure_id
            failure_map[failure_id] = {"name": failure.failure_type, "value": 0}

        results = (session.query(
            FailureImage.failure_id,
            func.COUNT(FailureImage.id).label("count")
        )
        .filter(FailureImage.panel_code.in_(panel_codes))
        .filter(not_(FailureImage.failure_id.in_([19, 112, 113, 114])))
        .group_by(FailureImage.failure_id)
        .all()
        )

        for row in results:
            failure_map[row.failure_id]["value"] = row.count
            res.append(failure_map[row.failure_id])

        t3 = time.time()
        print("饼状图最终结果, 花费时间: ", t3-t1)
        
        return res

# 超出有效区域就堆在有效区域的边缘
def generate_rectangle_coordinates(x0, y0, x1, y1):
    """
    根据矩形框的左上角和右下角生成所有整数坐标点。
    :param x0: 左上角 x 坐标
    :param y0: 左上角 y 坐标
    :param x1: 右下角 x 坐标
    :param y1: 右下角 y 坐标
    :return: 矩形框内的所有整数坐标点 (N, 2) 数组
    """
    xs = np.arange(x0, x1 + 1)
    ys = np.arange(y0, y1 + 1)
    xx, yy = np.meshgrid(xs, ys)
    coordinates = np.vstack([xx.ravel(), yy.ravel()]).T
    return coordinates


# 只保留有效区域的坐标
def get_intersection(rect_a, rect_b):
    """
    获取两个矩形框的相交区域。
    :param rect_a: 矩形框 A 的范围 (x0, y0, x1, y1)
    :param rect_b: 矩形框 B 的范围 (x0, y0, x1, y1)
    :return: 相交区域的范围 (x0, y0, x1, y1)，如果没有相交则返回 None
    """
    # 解构矩形框 A 和 B 的边界
    a_x0, a_y0, a_x1, a_y1 = rect_a
    b_x0, b_y0, b_x1, b_y1 = rect_b

    # 计算相交区域的边界
    intersect_x0 = max(a_x0, b_x0)  # 左边界取最大值
    intersect_y0 = max(a_y0, b_y0)  # 上边界取最大值
    intersect_x1 = min(a_x1, b_x1)  # 右边界取最小值
    intersect_y1 = min(a_y1, b_y1)  # 下边界取最小值

    # 检查是否有相交区域
    if intersect_x0 <= intersect_x1 and intersect_y0 <= intersect_y1:
        return (intersect_x0, intersect_y0, intersect_x1, intersect_y1)
    else:
        return None  # 没有相交区域


# 按照指定比例拆分原图为多个子图，获取子图的堆叠最大值
def panel_pixel_heatmap_task(period_type, start_time=None, top_3=None):
    # start_time = datetime(2025, 3, 17, 17, 53)


    # 缩放倍数
    scale = 30
    t1 = time.time()

    if scp_ip=="192.168.2": 
        background_image = "/sda2T/data/oqc/White.bmp"
        server_host = "192.168.2.71"
    else:
        background_image = "/mnt/md126/jd/White.bmp"
        server_host = "172.168.0.246"
    # x0, y0, w, h
    img_roi = [925, 1409, 5020, 1026]
    # img_roi = [1000, 1409, 4870, 1026]

    roi_x0, roi_y0, roi_x1, roi_y1 = [img_roi[0], img_roi[1], img_roi[0]+img_roi[2], img_roi[1]+img_roi[3]]

    # print("有效区域", roi_x0, roi_y0, roi_x1, roi_y1)
    
    image_url = f"http://{server_host}:8869{background_image}"

    # 读取图像远程文件
    cap = cv2.VideoCapture(image_url)
    _, cv_image = cap.read()
    cap.release()

    # 原图的宽高
    height, width, _ = cv_image.shape

    # 缩放后的有效数据
    width_num = int(width/scale)
    height_num = int(height/scale)

    # 取整
    width = width_num*scale
    height = height_num*scale

    with SessionLocal() as session:
        panel_images = panel_iamge_filter_by_period(period_type, session, start_time, True)
        i = 0

        if top_3:
            origin_heatmap = {}
            vh_heatmap = {}
            res_heatmap = {}
            max_value = {}

        else:
            origin_heatmap = np.zeros((width, height))
            vh_heatmap = np.zeros((width_num, height_num))
            vh_vh = []

        for panel_image in panel_images:
            
            detection_info = json.loads(panel_image.detection_info) if panel_image.detection_info else []

            for box_item in detection_info:
                failure_id = box_item["failure_id"]
                # 过滤条件有错误类型的时候，只显示指定错误类型的错误
                if failure_id not in [18, 19, 112, 113, 114]:
                    box = box_item["box"]
                    x0 = box[0]
                    y0 = box[1]
                    x1 = box[2]
                    y1 = box[3]
                    
                    res = get_intersection((x0, y0, x1, y1), (roi_x0, roi_y0, roi_x1, roi_y1))

                    if not res:
                        continue

                    rect_coords = generate_rectangle_coordinates(*res)

                    if top_3:
                        origin_heatmap[failure_id] = origin_heatmap.get(failure_id, np.zeros((width, height)))
                        vh_heatmap[failure_id] = vh_heatmap.get(failure_id, np.zeros((width_num, height_num)))
                        np.add.at(origin_heatmap[failure_id], (rect_coords[:, 0], rect_coords[:, 1]), 1)
                    else:
                        np.add.at(origin_heatmap, (rect_coords[:, 0], rect_coords[:, 1]), 1)
                    i += 1

                    # # 生成矩形框的所有坐标点
                    # rect_coords = generate_rectangle_coordinates(x0, y0, x1, y1)
                    # if i<100:
                    #     print(f"错误点坐标：({x0}, {y0}, {x1}, {y1}), 错误点类型: {failure_id}")
                    #     print(f"生成的矩形框的坐标点个数：{len(rect_coords)}")
                    #     # 如果检测框超出有效区域, 就让检测框落在有效区域
                    #     np.clip(rect_coords[:, 0], roi_x0, roi_x1, out=rect_coords[:, 0])
                    #     np.clip(rect_coords[:, 1], roi_y0, roi_y1, out=rect_coords[:, 1])
                    

        print(f"一共有{i}个错误类型")
        # print(origin_heatmap)

        if top_3:
            top_three_origin_heatmap = sorted(origin_heatmap.items(), key=lambda item: np.max(item[1]), reverse=True)[:3]

            for filure_id, origin_heatmap in top_three_origin_heatmap:
                vh_heatmap = np.zeros((width_num, height_num))
                i = 0
                for v_h in np.vsplit(origin_heatmap, width_num):
                    j = 0
                    for vh_h in np.hsplit(v_h, height_num):
                        vh_heatmap[i, j] = np.max(vh_h)
                        j += 1
                    i += 1
                
                shape = vh_heatmap.shape
                max_value[filure_id] = np.max(vh_heatmap)

                if max_value[filure_id] > 0:

                    # 只返回有错误的像素点坐标
                    indices = np.where(vh_heatmap >= max_value[filure_id]*0.6) 

                    # res_heatmap = [[int(i), int(j), int(vh_heatmap[i, j]), vh_vh[i][j]] for i, j in zip(indices[0], indices[1])]   
                    res_heatmap[filure_id] = [[int(i), int(j), int(vh_heatmap[i, j])] for i, j in zip(indices[0], indices[1])]

            t2 = time.time()
            # print(f"top3 热力图总耗时: {t2-t1}")
            return {"max_value": max_value, "shape": (width_num, height_num), "image_url": image_url, "img_roi": img_roi, "heatmap": json.dumps(res_heatmap)}
        else:
            i = 0
            for v_h in np.vsplit(origin_heatmap, width_num):
                # vh_vh.append([])
                j = 0
                for vh_h in np.hsplit(v_h, height_num):
                    vh_heatmap[i, j] = np.max(vh_h)
                    # vh_vh[i].append(vh_h.tolist())
                    j += 1
                i += 1


            shape = vh_heatmap.shape
            max_value = np.max(vh_heatmap)

            if max_value > 0:

                # 只返回有错误的像素点坐标
                # indices = np.where(heatmap > 10) 
                indices = np.where(vh_heatmap >= max_value*0.6) 

                res_heatmap = [[int(i), int(j), int(vh_heatmap[i, j])] for i, j in zip(indices[0], indices[1])]
            else:
                return {"max_value": 0, "shape": shape, "image_url": image_url, "img_roi": img_roi}    

            t2 = time.time()

            # print(f"热力图总耗时: {t2-t1}")

            return {"max_value": max_value, "heatmap": str(res_heatmap), "shape": shape, "image_url": image_url, "img_roi": img_roi}       
        

# 按照指定比例拆分原图为多个子图，获取子图的堆叠最大值
def chip_pixel_heatmap_task(chip_type, period_type, start_time=None):
    
    if not period_type:
        period_type = 0

    # if not start_time:
    #     start_time = datetime(2025, 3, 21, 17, 53)

    chip_list = []

    for i in range(1, 4):
        chip_list.append(f"{chip_type}{i}")

    # 缩放倍数
    scale = 30
    t1 = time.time()

    if scp_ip=="192.168.2": 
        background_image = "/sda2T/data/jd/upload/P504guojian0317bmp/20250317/102655_NJ5D1108LG1_NG/White(WHITE).bmp"
        server_host = "192.168.2.71"
    else:
        background_image = "/mnt/md126/jd/White.bmp"
        server_host = "172.168.0.246"
    # x0, y0, w, h
    img_roi = [925, 1409, 5020, 1026]
    # img_roi = [1000, 1409, 4870, 1026]

    roi_x0, roi_y0, roi_x1, roi_y1 = [img_roi[0], img_roi[1], img_roi[0]+img_roi[2], img_roi[1]+img_roi[3]]

    # print("有效区域", roi_x0, roi_y0, roi_x1, roi_y1)
    
    image_url = f"http://{server_host}:8869{background_image}"

    # 读取图像远程文件
    cap = cv2.VideoCapture(image_url)
    _, cv_image = cap.read()
    cap.release()

    # 原图的宽高
    height, width, _ = cv_image.shape

    # 缩放后的有效数据
    width_num = int(width/scale)
    height_num = int(height/scale)

    # 取整
    width = width_num*scale
    height = height_num*scale

    origin_heatmap = {}
    vh_heatmap = {}
    res_heatmap = {}
    max_value = {}
    panel_nums = {}

    with SessionLocal() as session:
        for chipname in chip_list:
            panel_images = panel_iamge_filter_by_period(period_type, session, start_time, material=True, chipname=chipname)

            panel_num = len(panel_images)
            panel_nums[chipname] = panel_num
            i = 0
            for panel_image in panel_images:
                
                detection_info = json.loads(panel_image.detection_info) if panel_image.detection_info else []

                for box_item in detection_info:
                    failure_id = box_item["failure_id"]
                    # 过滤条件有错误类型的时候，只显示指定错误类型的错误
                    if failure_id not in [18, 19, 112, 113, 114]:
                        box = box_item["box"]
                        x0 = box[0]
                        y0 = box[1]
                        x1 = box[2]
                        y1 = box[3]
                        
                        res = get_intersection((x0, y0, x1, y1), (roi_x0, roi_y0, roi_x1, roi_y1))

                        if not res:
                            continue

                        rect_coords = generate_rectangle_coordinates(*res)

                        origin_heatmap[chipname] = origin_heatmap.get(chipname, np.zeros((width, height)))
                        vh_heatmap[chipname] = vh_heatmap.get(chipname, np.zeros((width_num, height_num)))
                        np.add.at(origin_heatmap[chipname], (rect_coords[:, 0], rect_coords[:, 1]), 1)

                        i += 1

            print(f"chip {chipname} 一共有 {i} 个错误类型")

            if i == 0:
                continue

            i = 0
            for v_h in np.vsplit(origin_heatmap[chipname], width_num):
                j = 0
                for vh_h in np.hsplit(v_h, height_num):
                    vh_heatmap[chipname][i, j] = np.max(vh_h)
                    j += 1
                i += 1
            
            shape = vh_heatmap[chipname].shape
            max_value[chipname] = np.max(vh_heatmap[chipname])

            if max_value[chipname] > 0:

                # 只返回有错误的像素点坐标
                indices = np.where(vh_heatmap[chipname] >= max_value[chipname]*0.6) 

                res_heatmap[chipname] = [[int(i), int(j), int(vh_heatmap[chipname][i, j])] for i, j in zip(indices[0], indices[1])] 
        if max_value:
            return {"max_value": max_value, "heatmap": json.dumps(res_heatmap), "shape": shape, "image_url": image_url, "img_roi": img_roi, "panel_num": panel_nums}
        else:
            return {"image_url": image_url, "img_roi": img_roi, "shape": [width_num, height_num]}

       
        



        
        